﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLite.NET;


namespace BierdopjeTV.API
{
    class BierdopjeEpisodes
    {
        #region Fields

        private int m_EpisodeID;
        private int m_ShowID;
        private string m_Titel;
        private int m_Season;
        private int m_Episode;
        private string m_AirDate;
        private string m_Summary;
        private int m_TVDBID;
        private int m_Votes;
        private double m_Score;
        private string m_SubsNL;
        private string m_SubsEN;
        
        #endregion

        #region Properties

        public int EpisodeID
        {
            get { return m_EpisodeID; }
            set { m_EpisodeID = value; }
        }

        public int ShowID
        {
            get { return m_ShowID; }
            set { m_ShowID = value; }
        }
        
        public string Titel
        {
            get { return m_Titel; }
            set { m_Titel = value; }
        }
        
        public int Season
        {
            get { return m_Season; }
            set { m_Season = value; }
        }

        public int Episode
        {
            get { return m_Episode; }
            set { m_Episode = value; }
        }

        public string AirDate
        {
            get { return m_AirDate; }
            set { m_AirDate = value; }
        }

        public string Summary
        {
            get { return m_Summary; }
            set { m_Summary = value; }
        }

        public int TVDBID
        {
            get { return m_TVDBID; }
            set { m_TVDBID = value; }
        }

        public double Score
        {
            get { return m_Score; }
            set { m_Score = value; }
        }

        public int Votes
        {
            get { return m_Votes; }
            set { m_Votes = value; }
        }

        public string SubsNL
        {
            get { return m_SubsNL; }
            set { m_SubsNL = value; }
        }

        public string SubsEN
        {
            get { return m_SubsEN; }
            set { m_SubsEN = value; }
        }

  
        #endregion

        #region Methods

 
        /// <summary>
        /// Add an show to the database :-)
        /// </summary>
        /// <param name="_Show"></param>
       // public static int addepisode(BierdopjeEpisodes _Episode)
        public static int WhichShowsNeedUpdate()
        {
            string _Query;
            _Query = "";
            _Query += "SELECT" + Environment.NewLine;
            _Query += "	DISTINCT Episodes.ShowId," + Environment.NewLine;
            _Query += "	Episodes.Season" + Environment.NewLine;
            _Query += "FROM" + Environment.NewLine;
            _Query += "	Episodes" + Environment.NewLine;
            _Query += "LEFT JOIN" + Environment.NewLine;
            _Query += "	BierdopjeEpisodes ON (" + Environment.NewLine;
            _Query += "		Episodes.ShowID = BierdopjeEpisodes.ShowID" + Environment.NewLine;
            _Query += "		AND" + Environment.NewLine;
            _Query += "		Episodes.Season =  BierdopjeEpisodes.Season" + Environment.NewLine;
            _Query += "		AND" + Environment.NewLine;
            _Query += "		Episodes.Episode =  BierdopjeEpisodes.Episode" + Environment.NewLine;
            _Query += "	)" + Environment.NewLine;
            _Query += "WHERE" + Environment.NewLine;
            _Query += "	Titel IS NULL" + Environment.NewLine;
            _Query += "ORDER BY" + Environment.NewLine;
            _Query += "	BierdopjeEpisodes.ShowID, BierdopjeEpisodes.Season, BierdopjeEpisodes.Episode" + Environment.NewLine;
            _Query += "" + Environment.NewLine;

            SQLiteResultSet _Results = Helpers.SqliteHelper.ExecuteQuery(_Query);
            int rijen = _Results.Rows.Count;
            //If there are episodes with no online data:
            if (rijen > 0)
            {
                for (int i = 0; i < rijen; i++)
                {
                    APIFunctions _APIEpisode =  new API.APIFunctions();                    
                    List<BierdopjeEpisodes> ApiEpisodes = _APIEpisode.GetEpisodesForSeason(Convert.ToInt32(_Results.Rows[i].fields[0]),
                                                                                       Convert.ToInt32(_Results.Rows[i].fields[1]));
                    UpdateBierdopjeEpisodes((List<BierdopjeEpisodes>)ApiEpisodes);
                }

                

            }

            return -1;
        }

        private static void UpdateBierdopjeEpisodes(List<BierdopjeEpisodes> _BierdopjeEpisodes)
        {
            //SQLiteClient _Client = new SQLiteClient(Program.DATABASE);

            string _Query;

            for (int i = 0; i <_BierdopjeEpisodes.Count; i++)
            {

                SQLiteResultSet _Results = Helpers.SqliteHelper.ExecuteQuery("SELECT * FROM BierdopjeEpisodes WHERE EpisodeID = " + Convert.ToInt32(_BierdopjeEpisodes[i].EpisodeID));
                //is episodeID found?
                if (_Results.Rows.Count > 0)
                {

                    _Query = "";
                    _Query += "UPDATE BierdopjeEpisodes SET" + Environment.NewLine;
                    _Query += "	ShowID = " +  _BierdopjeEpisodes[i].ShowID + "," + Environment.NewLine;
                    _Query += "	Titel = '" +  dbSave(_BierdopjeEpisodes[i].Titel) + "'," + Environment.NewLine;
                    _Query += "	Season = " + _BierdopjeEpisodes[i].Season + "," + Environment.NewLine;
                    _Query += "	Episode = " + _BierdopjeEpisodes[i].Episode + "," + Environment.NewLine;
                    _Query += "	AirDate = '" +  dbSave(_BierdopjeEpisodes[i].AirDate) + "'," + Environment.NewLine;
                    _Query += "	TVDBID = " + _BierdopjeEpisodes[i].TVDBID + "," + Environment.NewLine;
                    _Query += "	Votes = '" + _BierdopjeEpisodes[i].Votes + "'," + Environment.NewLine;
                    _Query += "	Score = '" + _BierdopjeEpisodes[i].Score + "'," + Environment.NewLine;
                    _Query += "	SubsNL = '" +  dbSave(_BierdopjeEpisodes[i].SubsNL) + "'," + Environment.NewLine;
                    _Query += "	SubsEN = '" +  dbSave(_BierdopjeEpisodes[i].SubsEN) + "'," + Environment.NewLine;
                    _Query += "	Summary = '" +  dbSave(_BierdopjeEpisodes[i].Summary) + "'" + Environment.NewLine;
                    _Query += "WHERE" + Environment.NewLine;
                    _Query += "	EpisodeID = " + _BierdopjeEpisodes[i].EpisodeID.ToString() + Environment.NewLine;
                    _Query += "" + Environment.NewLine;

                    System.Diagnostics.Debug.Print(_Query);
                    Helpers.SqliteHelper.ExecuteQuery(_Query);

                }
                else //add the episode
                {
                    _Query = "";
                    _Query += "INSERT INTO BierdopjeEpisodes" + Environment.NewLine;
                    _Query += "(" + Environment.NewLine;
                    _Query += "	EpisodeID," + Environment.NewLine;
                    _Query += "	ShowID," + Environment.NewLine;
                    _Query += "	Titel," + Environment.NewLine;
                    _Query += "	Season," + Environment.NewLine;
                    _Query += "	Episode," + Environment.NewLine;
                    _Query += "	AirDate," + Environment.NewLine;
                    _Query += "	TVDBID," + Environment.NewLine;
                    _Query += "	Votes," + Environment.NewLine;
                    _Query += "	Score," + Environment.NewLine;
                    _Query += "	Watched," + Environment.NewLine;
                    _Query += "	SubNLDownloaded," + Environment.NewLine;
                    _Query += "	SubENDownloaded," + Environment.NewLine;
                    _Query += "	SubsNL," + Environment.NewLine;
                    _Query += "	SubsEN," + Environment.NewLine;
                    _Query += "	Summary" + Environment.NewLine;
                    _Query += ")" + Environment.NewLine;
                    _Query += "VALUES" + Environment.NewLine;
                    _Query += "(" + Environment.NewLine;
                    _Query += "	" + _BierdopjeEpisodes[i].EpisodeID + "," + Environment.NewLine;
                    _Query += "	" + _BierdopjeEpisodes[i].ShowID + "," + Environment.NewLine;
                    _Query += "	'" + dbSave(_BierdopjeEpisodes[i].Titel) + "'," + Environment.NewLine;
                    _Query += "	" + _BierdopjeEpisodes[i].Season + "," + Environment.NewLine;
                    _Query += "	" + _BierdopjeEpisodes[i].Episode + "," + Environment.NewLine;
                    _Query += "	'" + dbSave(_BierdopjeEpisodes[i].AirDate) + "'," + Environment.NewLine;
                    _Query += "	" + _BierdopjeEpisodes[i].TVDBID + "," + Environment.NewLine;
                    _Query += "	'" + _BierdopjeEpisodes[i].Votes + "'," + Environment.NewLine;
                    _Query += "	'" + _BierdopjeEpisodes[i].Score + "'," + Environment.NewLine;
                    _Query += "	0," + Environment.NewLine;
                    _Query += "	0," + Environment.NewLine;
                    _Query += "	0," + Environment.NewLine;                    
                    _Query += "	'" + dbSave(_BierdopjeEpisodes[i].SubsNL) + "'," + Environment.NewLine;
                    _Query += "	'" + dbSave(_BierdopjeEpisodes[i].SubsEN) + "'," + Environment.NewLine;                    
                    _Query += "	'" + dbSave(_BierdopjeEpisodes[i].Summary) + "'" + Environment.NewLine;
                    _Query += ")" + Environment.NewLine;
                    
                    Helpers.SqliteHelper.ExecuteQuery(_Query);
                }
                                                      
            //int rijen = _Results.Rows.Count;
            //If there are episodes with no online data:           
            }
         
        }

        public static void UpdateEpisodes(int ShowID, int Season)
        {
            string _Query;

            _Query = "";
            _Query += "SELECT" + Environment.NewLine;
            _Query += "	DISTINCT ShowID," + Environment.NewLine;
            _Query += "	Season" + Environment.NewLine;
            _Query += "FROM" + Environment.NewLine;
            _Query += "	BierdopjeEpisodes" + Environment.NewLine;
            _Query += "WHERE" + Environment.NewLine;
            _Query += "	ShowID = " + ShowID.ToString() + Environment.NewLine;
            if (Season != 0)
            {
                _Query += "	AND" + Environment.NewLine;
                _Query += "	Season = " + Season.ToString() +  Environment.NewLine;
            }

            SQLiteResultSet _Results = Helpers.SqliteHelper.ExecuteQuery(_Query);

            int rijen = _Results.Rows.Count;
            //If there are episodes with no online data:
            if (rijen > 0)
            {
                for (int i = 0; i < rijen; i++)
                {
                    APIFunctions _APIEpisode = new API.APIFunctions();
                    //List<BierdopjeEpisodes> _BierdopjeEpisodes = new List<BierdopjeEpisodes>();

                    List<BierdopjeEpisodes> ApiEpisodes = _APIEpisode.GetEpisodesForSeason(ShowID,
                                                                                       Convert.ToInt32(_Results.Rows[i].fields[1]));
                    UpdateBierdopjeEpisodes((List<BierdopjeEpisodes>)ApiEpisodes);
                }



            }

        }

        private static string dbSave(string source)
        {
            return source.Replace("'", "''");
        }


        #endregion
    }
}
